/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package donnees;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import sun.rmi.server.Dispatcher;
import server.Cpu;
import server.Memoire;
import server.Methode;
import server.Processus;

/**
 *
 * @author delpech.j
 */
public class Bdd {

    //base de donnees : javaweb, utilisateur : javaweb, mot de passe : javaweb
    private String madb = "javaweb", user = "javaweb", mdp = "javaweb";
    private Connection connect = null;
    private PreparedStatement preparedStatement = null;
    private Statement statement, statementTemp;
    private ResultSet resultSet, resultSetTemp;

    //Connexion a la base de donnees derby
    public void connect() {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {

            connect = DriverManager.getConnection("jdbc:derby://localhost:1527/" + madb + ";user=" + user + ";password=" + mdp);
            System.out.println("Connexion OK");
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * ************ AJOUT DANS LA BDD ***************************
     */
    //methode d'ajout de la mmoire utilisee, libre, totale dans la base
    public void addMemoryBDD(String serv, String f, String u, String t) {
        System.out.println(" Debut addMemoryBDD");
        int id = makeId("memoire", "id_mem");

        connect();
        //insertion du resultat dans la table memoire
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into " + madb + ".memoire (id_mem,freemem, usedmem, totalmem) values (" + id + ",'" + f + "','" + u + "','" + t + "')");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close();
        }
        int id_methode = makeId("methode", "id_methode");
        connect();
        //insertion de la methode getFreeMemory dans la table methode, lie a la table memoire
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into " + madb + ".methode (id_methode, date, rmiserver, methode, id_mem, id_cpu, id_tache) values (" + id_methode + ",current_timestamp,'" + serv + "','getFreeMemory','" + id + "','0','0')");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close();
        }

    }

    //methode d'ajout dans la base, pourcentage de CPU
    public void addCpuBDD(String serv, String cpu) {
        System.out.println(" Debut addCpuBDD");
        int id = makeId("cpu", "id_cpu");

        connect();
        //insertion du resultat dans la table cpu
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into " + madb + ".cpu (id_cpu,cpu) values (" + id + ",'" + cpu + "')");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close();
        }

        int id_methode = makeId("methode", "id_methode");
        connect();
        //insertion de la methode getCPU dans la table methode, lie a la table CPU
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into " + madb + ".methode (id_methode, date, rmiserver, methode, id_mem, id_cpu, id_tache) values (" + id_methode + ",current_timestamp,'" + serv + "','getCpu','" + 0 + "','" + id + "','" + 0 + "')");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close();
        }

    }

    //methode d'ajout de taches dans la base
    public void addTaskBDD(String serv, List<String> temp) {
        System.out.println(" Debut addTaskBDD");
        int id = makeId("tache", "id_tache");
        connect();
        for (int i = 0; i < temp.size(); i++) {
            try {
                if (!checkStringExists(temp.get(i), "tache", "tache", id)) {
                    preparedStatement = connect.prepareStatement(
                            "insert into " + madb + ".tache (id_tache, tache) values (" + id + ",'" + temp.get(i) + "')");
                    preparedStatement.executeUpdate();
                }
            } catch (Exception ex) {
                Logger.getLogger(Bdd.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        int id_methode = makeId("methode", "id_methode");
        connect();
        //insertion de la methode getTaskList dans la table methode, lie a la table tache
        try {
            preparedStatement = connect.prepareStatement(
                    "insert into " + madb + ".methode (id_methode, date, rmiserver, methode, id_mem, id_cpu, id_tache) values (" + id_methode + ",current_timestamp,'" + serv + "','getTaskList','" + 0 + "','" + 0 + "','" + id + "')");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close();
        }

    }

    /**
     * ************ SUPPRESSION DU CONTENU DE LA BDD
     * ***************************
     */
    //suppression de toutes les entrÃ©es existantes dans la base de donnÃ©es
    public void deleteHistory() {
        System.out.println(" Debut deleteHistory");
        connect();
        try {
            preparedStatement = connect.prepareStatement(
                    "delete from " + madb + ".tache");
            preparedStatement.executeUpdate();

            preparedStatement = connect.prepareStatement(
                    "delete from " + madb + ".cpu");
            preparedStatement.executeUpdate();

            preparedStatement = connect.prepareStatement(
                    "delete from " + madb + ".memoire");
            preparedStatement.executeUpdate();

            preparedStatement = connect.prepareStatement(
                    "delete from " + madb + ".methode");
            preparedStatement.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            close();
        }
    }

    /**
     * ************ SELECTION DANS LA BDD ***************************
     */
    //Historique
    public List<Methode> getListMethodesBDD() {
        System.out.println(" Debut getMethode");
        Methode methodeCourante = null;
        ArrayList<Methode> listeMethodes = new ArrayList<Methode>();
        try {
            connect();
            statement = connect.createStatement();
            resultSet = statement.executeQuery("select * from " + madb + ".methode");
            while (resultSet.next()) {
                System.out.println(resultSet.getString("date"));
                System.out.println(resultSet.getString("rmiserver"));
                System.out.println(resultSet.getString("methode"));
                System.out.println(resultSet.getString("id_mem"));
                System.out.println(resultSet.getString("id_cpu"));
                methodeCourante = new Methode();
                methodeCourante.setDate(resultSet.getString("date"));
                methodeCourante.setServer(resultSet.getString("rmiserver"));
                methodeCourante.setMethode(resultSet.getString("methode"));
                if (resultSet.getInt("id_mem") != 0) {
                    methodeCourante.setId(resultSet.getString("id_mem"));
                }
                if (resultSet.getInt("id_cpu") != 0) {
                    methodeCourante.setId(resultSet.getString("id_cpu"));
                }
                if (resultSet.getInt("id_tache") != 0) {
                    methodeCourante.setId(resultSet.getString("id_tache"));
                }
                //if(resultSet.getInt("id_methode")<0)
                listeMethodes.add(methodeCourante);
            }
        } catch (Exception ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }
        return listeMethodes;

    }

    public Memoire getMemoryBDD(int id) {
        System.out.println(" Debut getMemory");
        Memoire mem = new Memoire();

        try {
            connect();
            statement = connect.createStatement();
            resultSet = statement.executeQuery("select * from " + madb + ".memoire where id_mem=" + id);
            while (resultSet.next()) {
                mem.setFree(resultSet.getString("freemem"));
                mem.setUsed(resultSet.getString("usedmem"));
                mem.setTotal(resultSet.getString("totalmem"));
            }
        } catch (Exception ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }
        return mem;

    }

    public Cpu getCpuBDD(int id) {
        System.out.println(" Debut getCpu");
        Cpu cpu = new Cpu();

        try {
            connect();
            statement = connect.createStatement();
            resultSet = statement.executeQuery("select * from " + madb + ".cpu where id_cpu=" + id);
            while (resultSet.next()) {
                cpu.setCpu(resultSet.getString("cpu"));
            }
        } catch (Exception ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);

        }
        return cpu;
    }

    public Processus getProcessusBDD(int id) {
        System.out.println(" Debut getProcessus");
        Processus tache = new Processus();

        try {
            connect();
            statement = connect.createStatement();
            resultSet = statement.executeQuery("select * from " + madb + ".tache where id_tache=" + id);

            while (resultSet.next()) {
                tache.addProcess(resultSet.getString("tache"));
            }
        } catch (Exception ex) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, ex);
        }
        return tache;
    }

//    select id_methode from methode
//              stock variable id_mem, id_cpu, id_task
//                      if(id_mem!=0)
//                        select free, used, totale from memoire where id_mem.mem = id_mem
//                                arraylist.add(free); arraylist.add(used); arraylist.add(total);
//                      if(id_cpu!=0) ...
//                      if(id_list!=0) ...    
//Verifier si l'id existe dans la table, renvoi vrai s'il existe    
    public boolean checkIdExists(int monid, String matable, String monchamp) throws Exception {
        System.out.println(" Debut checkIdExists");
        try {
            connect();
            statement = connect.createStatement();
            resultSet = statement.executeQuery("select * from " + madb + "." + matable + " where " + monchamp + "=" + monid + "");
            while (resultSet.next()) {
                int test = resultSet.getInt(monchamp);

                if (monid == test) {
                    return true;
                }
            }
        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }
        return false;
    }

    public boolean checkStringExists(String monstring, String matable, String monchamp, int id) throws Exception {
        System.out.println(" Debut checkStringExists");
        try {
            connect();
            statementTemp = connect.createStatement();
            resultSetTemp = statementTemp.executeQuery("select * from " + madb + "." + matable + " where " + monchamp + "='" + monstring + "' AND id_tache=" + id + "");
            while (resultSetTemp.next()) {
                String test = resultSetTemp.getString(monchamp);

                if (monstring.equals(test)) {
                    return true;
                }
            }
        } catch (Exception e) {
            throw e;
        }

        return false;
    }

//Fonction de creation de l'id memoire/cpu/tache
    public int makeId(String matable, String monchamp) {
        //on commence a 1, car dans methode, l'id 0 correspond a "non utilise"
        int id = 1;
        System.out.println(" Debut makeId");

        //tant que l'id existe, on incremente de 1
        while (true) {
            try {
                if (checkIdExists(id, matable, monchamp)) {
                    id++;
                } else {
                    break;
                }
            } catch (Exception ex) {
                System.out.println(ex.getMessage());
                // plutot important ce break !!!!
                break;
            }
        }
        return id;
    }

    //fonction qui ferment les connexions ouverte avec la base de donnÃ©es
    private void close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }

            if (connect != null) {
                connect.close();
            }

            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (Exception e) {
            Logger.getLogger(Dispatcher.class.getName()).log(Level.SEVERE, null, e);
        }
    }

}
